# -*- coding: utf-8 -*-

"""
    Sahana Eden Vulnerability Controller
"""

module = request.controller
resourcename = request.function

if not settings.has_module(module):
    raise HTTP(404, body="Module disabled: %s" % module)

# @ToDo: deployment_setting
#countries = ["TL", "VN"]
countries = ["VN"]

#DEBUG = True
#s3_debug = s3base.s3_debug

# -----------------------------------------------------------------------------
def init():
    """
        Create the static GeoJSONs that the app needs
    """

    gis.export_admin_areas(countries)
    return "complete"

# -----------------------------------------------------------------------------
def index():
    """ Module Home Page: Map """

    # This module uses it's own Theme
    settings.base.theme = "Vulnerability"

    # Additional scripts
    append = s3.scripts.append
    append("/%s/static/scripts/yepnope.1.5.4-min.js" % appname)
    append("/%s/static/scripts/jit/jit-yc.js" % appname)
    append("/%s/static/scripts/S3/s3.gis.loader.js" % appname)
    if s3.debug:
        append("/%s/static/scripts/jquery.ui.selectmenu.js" % appname)
        #append("/%s/static/scripts/jquery.ui.progressbar.js" % appname)
        append("/%s/static/scripts/TypeHelpers.js" % appname)
        append("/%s/static/themes/Vulnerability/js/s3.vulnerability.js" % appname)
        append("/%s/static/themes/Vulnerability/js/s3.reports.js" % appname)
        append("/%s/static/themes/Vulnerability/js/s3.analysis.js" % appname)
        append("/%s/static/themes/Vulnerability/js/s3.treemap.js" % appname)
        append("/%s/static/scripts/jquery.dataTables.js" % appname)
        append("/%s/static/scripts/jquery.dataTables.fnSetFilteringDelay.js" % appname)
        append("/%s/static/scripts/S3/s3.dataTables.js" % appname)
        append("/%s/static/scripts/flot/jquery.flot.js" % appname)
        append("/%s/static/scripts/flot/jquery.flot.fillbetween.js" % appname)
        append("/%s/static/scripts/flot/jquery.flot.crosshair.js" % appname)
    else:
        append("/%s/static/themes/Vulnerability/js/s3.vulnerability.min.js" % appname)
        append("/%s/static/scripts/S3/s3.dataTables.min.js" % appname)
        append("/%s/static/scripts/flot/jquery.flot.min.js" % appname)
        append("/%s/static/scripts/flot/jquery.flot.fillbetween.min.js" % appname)
        append("/%s/static/scripts/flot/jquery.flot.crosshair.min.js" % appname)

    js_global = []
    append = js_global.append

    # i18n
    i18n = "\n".join((
        "i18n.all='%s'" % T("All"),
        "i18n.gis_requires_login='%s'" % T("Requires Login"),
        "i18n.no_matching_result='%s'" % T("No matching result"),
        "i18n.no_entries_found='%s'" % T("No Entries Found"),
        "i18n.loading_report_details='%s'" % T("Loading report details"),
        "i18n.choose='%s'" % T("Choose"),
        "i18n.population='%s'" % T("Population"),
        "i18n.reported='%s'" % T("Reported"),
        "i18n.country='%s'" % COUNTRY,
        "i18n.country_in='%s'" % T("Country in"),
        "i18n.select_country='%s'" % T("Select a Country"),
        "i18n.show_more='%s'" % T("Show more"),
        "i18n.show_less='%s'" % T("Show less"),
        "i18n.submit_data='%s'" % T("Submit Data"),
        "i18n.analysis='%s'" % T("Analysis"),
        "i18n.reports='%s'" % T("Reports"),
        "i18n.all_reports='%s'" % T("All reports"),
        "i18n.my_reports='%s'" % T("My reports"),
        "i18n.approval_request_submitted='%s'" % T("Approval request submitted"),
        "i18n.thankyou_for_your_approval='%s'" % T("Thank you for your approval"),
        "i18n.reject_request_submitted='%s'" % T("Reject request submitted"),
        "i18n.submission_has_been_declined='%s'" % T("Thank you, the submission%(br)shas been declined") % dict(br="<br />"),
        "i18n.last_data_collected_on='%s'" % T("Last Data Collected on"),
        "i18n.by='%s'" % T("by"),
        "i18n.in_='%s'" % T("in"),
        "i18n.in_this='%s'" % T("in this"),
        "i18n.of='%s'" % T("of"),
        "i18n.out_of='%s'" % T("out of"),
        "i18n.review='%s'" % T("Review"),
        "i18n.submitted_by='%s'" % T("submitted by"),
        "i18n.go_to_the='%s'" % T("Go to the"),
        "i18n.select_data_type='%s'" % T("Select data type"),
        "i18n.about_to_submit_indicator_ratings='%s'" % T("You are about to submit indicator ratings for"),
        "i18n.poor='%s'" % T("poor"),
        "i18n.fair='%s'" % T("fair"),
        "i18n.moderate='%s'" % T("moderate"),
        "i18n.strong='%s'" % T("strong"),
        "i18n.data_quality='%s'" % T("Data Quality"),
        "i18n.of_total_data_reported='%s'" % T("of total data reported"),
        "i18n.uploading_report_details='%s'" % T("Uploading report details"),
        "i18n.upload_successful='%s'" % T("Upload successful"),
        "i18n.no_data='%s'" % T("No Data"),
        "i18n.extrapolated='%s'" % T("Extrapolated"),
        ))
    append(i18n)

    # Save data in the session for later
    table = s3db.vulnerability_aggregated_indicator
    query = (table.uuid == "Resilience")
    result = db(query).select(table.parameter_id, limitby=(0, 1))
    if result:
        session.s3.resilience_id = result.first().parameter_id

    dtable = s3db.stats_demographic
    query = (dtable.name == "Population")
    result = db(query).select(dtable.parameter_id, limitby=(0, 1))
    if result:
        session.s3.population_id = result.first().parameter_id

    # Get the list of indicators
    itable = db.vulnerability_indicator
    rows = db(itable.deleted == False).select(itable.name,
                                              itable.description,
                                              itable.parameter_id,
                                              orderby=itable.posn)
    pids = []
    pappend = pids.append
    indicators = OrderedDict()
    count = 1
    for row in rows:
        pappend(row.parameter_id)
        indicators[count] = dict(i=row.parameter_id,
                                 n=row.name,
                                 d=row.description)
        count += 1

    append('''\nidata=%s''' % json.dumps(indicators))
    session.s3.indicator_pids = pids

    # Get the L0 hdata & summary vdata
    hdata, vdata = l0()

    # Get the default location to open the map
    bounds = None
    root_org = auth.root_org()
    start = False
    if root_org:
        otable = s3db.org_organisation
        ttable = s3db.gis_location_tag
        gtable = db.gis_location
        query = (otable.id == root_org) & \
                (ttable.tag == "ISO2") & \
                (ttable.value == otable.country)
        r = db(query).select(ttable.location_id,
                             limitby=(0, 1)).first()
        if r and r.location_id in countries:
            start = True
            append('''\nstart=%s''' % r.location_id)
            # Add the child L1 summary vdata
            l1(r.location_id, vdata)
    if not start:
        append('''\nstart=""''')

    dumps = json.dumps
    script = '''
hdata=%s
vdata=%s''' % (dumps(hdata), dumps(vdata))
    append(script)

    s3.js_global.append("".join(js_global))

    # Reports
    # These get pulled-in via AJAX
    # from s3.s3data import S3DataTable
    # resource = s3db.resource("vulnerability_document")
    # list_fields = ["id",
                   # "date",
                   # "location_id",
                   # "location_id$L2",
                   # "source_id"
                   # "document_type",
                   # "created_by",
                   # "approved_by",
                   # ]
    # rfields = resource.resolve_selectors(list_fields)[0]
    # filteredrows = resource.count()
    # dt = S3DataTable(rfields, [], orderby=~s3db.vulnerability_document.date)
    # level_1_titles = [["Approval pending", T("Approval pending")],
                      # ["VCA Report", T("VCA Report")],
                      # ["Report", T("Report")],
                      # ]
    # report = dt.html(filteredrows,
                     # filteredrows,
                     # "report",
                     # dt_pagination = "false",
                     # dt_bFilter = "false",
                     # dt_sDom = "t",
                     # dt_group = [4, 3],
                     # dt_group_totals = [level_1_titles],
                     # dt_ajax_url = URL(c="vulnerability",
                                       # f="report",
                                       # extension="aadata",
                                       # vars={"id": "report"},
                                       # ),
                     # dt_action_col = -1,
                     # dt_group_space = "true",
                     # dt_shrink_groups = "accordion",
                     # dt_group_types = ["text", "none"],
                     # )
    # s3.report = report

    # TreeMap
    s3.stylesheets.append("jit/base.css")

    user = auth.user
    if user:
        user_name = "%s %s" % (user.first_name, user.last_name)
    else:
        user_name = ""
    today = request.utcnow.strftime("%d-%b-%y")

    response.view = "vulnerability/map.html"
    return dict(indicators=indicators,
                user_name = user_name,
                today = today,
                COUNTRY = COUNTRY.upper(),
                CHOOSE_COUNTRY = T("Choose Country"))

# -----------------------------------------------------------------------------
def l0():
    """
        Return hdata (Hierarchy Labels) & summary vdata (Resilience) for all Countries
        - used only by the initial map load
    """

    gtable = db.gis_location
    ttable = s3db.gis_location_tag
    htable = s3db.gis_hierarchy
    query = (gtable.id == ttable.location_id) & \
            (ttable.tag == "ISO2") & \
            (ttable.value.belongs(countries)) & \
            (gtable.id == htable.location_id)
    atable = s3db.vulnerability_aggregate
    lquery = (atable.parameter_id == session.s3.resilience_id) & \
             (atable.agg_type == 4) & \
             (atable.location_id == gtable.id)
    left = atable.on(lquery)
    hdata = {}
    vdata = {}

    ids = []
    append = ids.append
    rows = db(query).select(gtable.id,
                            gtable.name,
                            htable.L1,
                            htable.L2,
                            htable.L3,
                            #htable.L4,
                            #atable.date,
                            atable.median,
                            orderby=~atable.date,
                            left=left)
    for row in rows:
        id = row[gtable].id
        if id in ids:
            # We're only interested in the most recent data per location
            continue
        append(id)
        _grow = row[gtable]
        _hrow = row[htable]
        hdata[id] = dict(l1 = _hrow.L1,
                         l2 = _hrow.L2,
                         l3 = _hrow.L3,
                         #l4 = _hrow.L4,
                         )
        median = row[atable].median
        if median is None:
            resilience = 0
        else:
            resilience = int(round(median, 0))
        vdata[id] = dict(r = resilience,
                         n = _grow.name,
                         l = 0,
                         )

    return hdata, vdata

# -----------------------------------------------------------------------------
def l1(id, vdata):
    """
        Update summary vdata (Resilience) for all child L1s of the start country
        - used only by the initial map load
    """

    gtable = db.gis_location
    query = (gtable.parent == id) & \
            (gtable.level == "L1")
    aitable = db.vulnerability_aggregated_indicator
    atable = db.vulnerability_aggregate
    rquery = (aitable.name == "Resilience") & \
             (atable.parameter_id == aitable.parameter_id) & \
             (atable.agg_type == 4)
    rows = db(query).select(gtable.id,
                            gtable.name,
                            )
    for row in rows:
        query = rquery & (atable.location_id == row.id)
        _row = db(query).select(#atable.date,
                                atable.median,
                                orderby=~atable.date).first()
        resilience = 0
        if _row and _row.median is not None:
            resilience = int(round(_row.median, 0))
        vdata[row.id] = dict(r = resilience,
                             n = row.name,
                             l = 1,
                             f = id,
                             )

    return

# -----------------------------------------------------------------------------
def vdata():
    """
        Return JSON of the Vulnerability data for a location
        - for display in Map Popups and the Drawer

        vdata = { id : {
                        'n' : name,
                        'l' : level,
                        'f' : parent,
                        'r' : resilience,
                        'i' : indicator data,
                        'c' : count (how many L3s reported in this region),
                        't' : count (how many L3s total in this region),
                        'q' : quality,
                        'p' : population,
                        's' : source (for population),
                        'b' : population breakdown (for L3s),
                        'd' : date last collected (for L3s),
                        'w' : collected by (for L3s),
                        'm' : images (for L3s),
                        }
                 }
    """

    try:
        id = request.args[0]
    except:
        raise HTTP(400)

    #if DEBUG:
    #   start = datetime.datetime.now()

    gtable = s3db.gis_location

    #if DEBUG:
    #    end = datetime.datetime.now()
    #    duration = end - start
    #    duration = "{:.2f}".format(duration.total_seconds())
    #    s3_debug("LocationModel load completed in %s seconds" % duration)
    #    start = datetime.datetime.now()

    query = (gtable.id == id)
    location = db(query).select(gtable.name,
                                gtable.level,
                                gtable.parent,
                                gtable.L0,
                                gtable.L1,
                                gtable.L2,
                                #gtable.L3,
                                limitby=(0, 1)).first()
    if not location or not location.level:
        return ""
    script = ""
    level = location.level
    data = dict(n = location.name,
                l = int(level[1]),
                f = location.parent,
                )
    
    #if DEBUG:
    #    end = datetime.datetime.now()
    #    duration = end - start
    #    duration = "{:.2f}".format(duration.total_seconds())
    #    s3_debug("Query 1 (location lookup) completed in %s seconds" % duration)
    #    start = datetime.datetime.now()

    # Represent numbers in the correct format
    nrepresent = IS_INT_AMOUNT().represent

    vdata = {}
    atable = s3db.vulnerability_aggregate
    resilience_id = session.s3.resilience_id

    #if DEBUG:
    #    end = datetime.datetime.now()
    #    duration = end - start
    #    duration = "{:.2f}".format(duration.total_seconds())
    #    s3_debug("StatsModel load completed in %s seconds" % duration)
    #    start = datetime.datetime.now()

    if level != "L3":
        # We need to read the ids, names & resiliences of the next level down for the selectmenu styling of the dropdown
        _level = int(level[1]) + 1
        query = (gtable.parent == id) & \
                (gtable.level == "L%s" % _level) & \
                (gtable.deleted == False)
        lquery = (atable.parameter_id == resilience_id) & \
                 (atable.agg_type == 4) & \
                 (atable.end_date == None) & \
                 (atable.location_id == gtable.id)
        left = atable.on(lquery)
        rows = db(query).select(gtable.id,
                                gtable.name,
                                #atable.date,
                                atable.median,
                                #atable.ward_count,
                                #atable.reported_count,
                                left=left)
        #if DEBUG:
        #    end = datetime.datetime.now()
        #    duration = end - start
        #    duration = "{:.2f}".format(duration.total_seconds())
        #    s3_debug("Query 2 (next level down) completed in %s seconds" % duration)
        #    start = datetime.datetime.now()

        for row in rows:
            grow = row[gtable]
            median = row[atable].median
            if median is None:
                resilience = 0
            else:
                resilience = int(round(median, 0))
            vdata[grow.id] = dict(r = resilience,
                                  n = grow.name,
                                  l = _level,
                                  f = id,
                                  )

        #if DEBUG:
        #    end = datetime.datetime.now()
        #    duration = end - start
        #    duration = "{:.2f}".format(duration.total_seconds())
        #    s3_debug("Query 2 (row in rows) completed in %s seconds" % duration)
        #    start = datetime.datetime.now()

    else:
        # We are an L3 already
        # Last Data Collected on d by w
        utable = auth.settings.table_user
        vtable = s3db.vulnerability_data
        query = (vtable.location_id == id)
        left = utable.on(utable.id == vtable.created_by)
        row = db(query).select(vtable.date,
                               utable.first_name,
                               utable.last_name,
                               orderby=~vtable.date,
                               left=left,
                               limitby=(0, 1)).first()
        if row:
            data["d"] = row[vtable].date.isoformat()
            user = row[utable]
            data["w"] = "%s %s" % (user.first_name, user.last_name)
        else:
            data["d"] = ""
            data["w"] = ""

        #if DEBUG:
        #    end = datetime.datetime.now()
        #    duration = end - start
        #    duration = "{:.2f}".format(duration.total_seconds())
        #    s3_debug("Query 2 (last data collection) completed in %s seconds" % duration)
        #    start = datetime.datetime.now()

    # Get the Resilience
    query = (atable.parameter_id == resilience_id) & \
            (atable.agg_type == 4) & \
            (atable.end_date == None) & \
            (atable.location_id == id)
    r = db(query).select(atable.date,
                         atable.median,
                         atable.ward_count,
                         atable.reported_count,
                         # Should be only one with end_date == None
                         #orderby=~atable.date,
                         limitby=(0, 1)).first()

    if not r or r.median is None:
        data["r"] = 0
        if level != "L3":
            data["c"] = 0
            data["q"] = "p"
            # Total number of L3s in this region
            data["t"] = nrepresent(len(gis.get_children(id, level="L3")))
    else:
        data["r"] = int(round(r.median, 0))
        # How many L3s have reported?
        reported_count = r.reported_count
        data["c"] = nrepresent(reported_count)
        # Total number of L3s in this region
        ward_count = r.ward_count
        data["t"] = nrepresent(ward_count)
        if level != "L3":
            # Calculate Quality
            if reported_count == 0 or ward_count == 0:
                q = "p"
            else:
                q = reported_count / ward_count
                if q < 0.25:
                    q = "p"
                elif q < 0.50:
                    q = "f"
                elif q < 0.75:
                    q = "m"
                else:
                    q = "s"
            data["q"] = q

    #if DEBUG:
    #    end = datetime.datetime.now()
    #    duration = end - start
    #    duration = "{:.2f}".format(duration.total_seconds())
    #    s3_debug("Query 3 (resilience) completed in %s seconds" % duration)
    #    start = datetime.datetime.now()

    # Get the aggregated data for this location for all indicators
    query = (atable.location_id == id) & \
            (atable.parameter_id.belongs(session.s3.indicator_pids))
    rows = db(query).select(atable.parameter_id,
                            atable.min,
                            atable.max,
                            atable.median,
                            orderby=~atable.date,
                            )
    indicator_data = {}
    pids = []
    pappend = pids.append
    for row in rows:
        pid = row.parameter_id
        if pid in pids:
            # We're only interested in the most recent data per indicator
            continue
        pappend(pid)
        indicator_data[pid] = dict(min = row.min,
                                   max = row.max,
                                   med = row.median,
                                   )
    data["i"] = indicator_data

    #if DEBUG:
    #    end = datetime.datetime.now()
    #    duration = end - start
    #    duration = "{:.2f}".format(duration.total_seconds())
    #    s3_debug("Query 4 (indicators) completed in %s seconds" % duration)
    #    start = datetime.datetime.now()

    # Get the Demographic data for the location
    ddtable = s3db.stats_demographic_data
    if level != "L3":
        # Just Population
        p = None
        if level != "L2":
            # Lookup direct
            query = (ddtable.location_id == id) & \
                    (ddtable.parameter_id == session.s3.population_id)
            row = db(query).select(ddtable.value,
                                   orderby=~ddtable.date,
                                   limitby=(0, 1)).first()
            if row:
                p = row.value
        if not p:
            # Fallback to an aggregate
            # @ToDo: mark this in some way - either '> p' or else '~p' by averaging from the data that we do have
            atable = s3db.stats_demographic_aggregate
            query = (atable.agg_type == 2) & \
                    (atable.location_id == id) & \
                    (atable.parameter_id == session.s3.population_id) & \
                    (atable.end_date == None)
            row = db(query).select(atable.sum,
                                   # Should be only one with end_date == None
                                   #orderby=~atable.date,
                                   limitby=(0, 1)).first()
            if row:
                p = row.sum
        data["p"] = nrepresent(p) if p else ""
    else:
        # L3: Population, Breakdowns & Source
        # Add all available breakdowns to the output
        b = {}
        dtable = s3db.stats_demographic
        query = (dtable.deleted != True) & \
                (dtable.name != "Population")
        demos = db(query).select(dtable.id,
                                 dtable.name)
        for d in demos:
            b[d.id] = dict(n = s3_unicode(T(d.name)),
                           v = "",
                           s = "")

        srctable = s3db.stats_source
        query = (ddtable.location_id == id) & \
                (ddtable.parameter_id == dtable.parameter_id) & \
                (ddtable.source_id == srctable.id)
        rows = db(query).select(dtable.id,
                                dtable.name,
                                ddtable.value,
                                srctable.name,
                                #ddtable.date,
                                orderby=~ddtable.date
                                )
        ids = []
        append = ids.append
        for row in rows:
            _id = row[dtable].id
            if _id in ids:
                # We're only interested in the most recent data per demographic
                continue
            append(_id)
            d = row[dtable]
            if d.name == "Population":
                data["p"] = nrepresent(row[ddtable].value)
                data["s"] = row[srctable].name
            else:
                # Breakdown
                b[_id]["v"] = nrepresent(row[ddtable].value)
                b[_id]["s"] = row[srctable].name

        data["b"] = b

        # Images
        itable = s3db.doc_image
        ttable = s3db.pr_image_library
        vdoc_table = s3db.vulnerability_document
        query = (vdoc_table.location_id == id) & \
                (vdoc_table.approved_by != None) & \
                (vdoc_table.document_type.belongs(("image", "map"))) & \
                (vdoc_table.doc_id == itable.doc_id) & \
                (ttable.original_name == itable.file)
        left = utable.on(utable.id == itable.created_by)
        images = db(query).select(itable.file,
                                  itable.comments,
                                  ttable.new_name,
                                  utable.first_name,
                                  utable.last_name,
                                  left=left,
                                  orderby=~itable.date)
        m = []
        mappend = m.append
        for image in images:
            i = image[itable]
            user = image[utable]
            mappend([image[ttable].new_name, i.file, i.comments,
                     "%s %s" % (user.first_name, user.last_name)])
        data["m"] = m

    #if DEBUG:
    #    end = datetime.datetime.now()
    #    duration = end - start
    #    duration = "{:.2f}".format(duration.total_seconds())
    #    s3_debug("Query 5 (demographics) completed in %s seconds" % duration)
    #    start = datetime.datetime.now()

    vdata[id] = data
    script = '''n=%s\n''' % json.dumps(vdata)
    response.headers["Content-Type"] = "application/json"
    return script

# -----------------------------------------------------------------------------
def rdata():
    """
        Controller to extract data for resilience analysis line graph

        returns a JavaScript like:

        r={"location_id":
                {"year":
                    {"indicator_index": [value, deviation]}
                }
          }

        where indicator_index is 0 for the overall resilience (median), or
        1-10 for the individual indicators (=index in the list + 1).

        Any data which are not available from the db will be omitted (to
        save bandwidth) - the client-side script must detect any missing
        keys itself.

        @todo: this controller must make sure that there is always a median
               (overall resilience) in each set => calculate if not present.
    """

    response.headers["Content-Type"] = "application/json"

    if not len(request.args):
        return '''n={}'''
    else:
        locations = list(set([a for a in request.args if a.isdigit()]))

    vars = request.get_vars
    fyear = None
    lyear = None
    if "after" in vars:
        try:
            fyear = int(vars["after"])
        except ValueError:
            pass
    if "before" in vars:
        try:
            lyear = int(vars["before"])
        except ValueError:
            pass

    if lyear and fyear and lyear > fyear:
        lyear, fyear = fyear, lyear
    if fyear:
        fdate = datetime.datetime(fyear, 1, 1)
    else:
        fdate = None
    if lyear:
        ldate = datetime.datetime(lyear + 1, 1, 1)
    else:
        ldate = request.utcnow

    resilience_id = session.s3.resilience_id
    indicator_pids = session.s3.indicator_pids
    pos = Storage([(indicator_pids[i], i + 1)
                   for i in xrange(len(indicator_pids))])
    pos[resilience_id] = 0

    atable = s3db.vulnerability_aggregate
    query = ((atable.parameter_id == resilience_id) & \
             (atable.agg_type == 4)) | \
            (atable.parameter_id.belongs(indicator_pids))

    if len(locations) == 1:
        query &= (atable.location_id == locations[0])
    else:
        query &= (atable.location_id.belongs(locations))
    if fyear:
        query &= (atable.date >= fdate)
    if lyear is None or lyear == request.utcnow.year:
        query &= ((atable.end_date < ldate) | (atable.end_date == None))
    else:
        query &= (atable.end_date < ldate)

    rows = db(query).select(atable.location_id,
                            atable.parameter_id,
                            atable.date,
                            atable.mean,
                            atable.median,
                            atable.mad,
                            orderby=~atable.date)

    keys = []
    seen = keys.append
    data = dict()
    for row in rows:
        l = row.location_id
        y = row.date.year
        p = pos[row.parameter_id]
        if (l, y, p) in keys:
            continue
        seen((l, y, p))

        if p == pos[resilience_id]:
            val = int(round(row.median, 0))
        else:
            val = row.median
        dev = row.mad

        if l not in data:
            ldata = data[l] = dict()
        else:
            ldata = data[l]
        if y not in ldata:
            ydata = ldata[y] = dict()
        else:
            ydata = ldata[y]
        ydata[p] = (val, dev)

    script = '''r=%s\n''' % json.dumps(data)
    return script

# -----------------------------------------------------------------------------
def tmdata():
    """ Controller to extract tree map data """

    MAX_LEVEL = 3 # the lowest level for child lookups

    # Requested locations
    if not len(request.args):
        response.headers["Content-Type"] = "application/json"
        return '''sdata={}'''
    else:
        locations = list(set([int(a) for a in request.args if a.isdigit()]))

    sdata = Storage()

    # Vulnerability Indicators
    indicator_pids = session.s3.indicator_pids
    idefaults = [(i, 0) for i in indicator_pids]

    # Locations Hierarchy
    ltable = s3db.gis_location
    parents = list(locations)
    children = list(locations)
    while parents or children:
        query = None
        if children:
            query = (ltable.id.belongs(children))
        if parents:
            q = (ltable.parent.belongs(parents))
            if query is None:
                query = q
            else:
                query |= q
        if query is None:
            break
        rows = db(query).select(ltable.id,
                                ltable.name,
                                ltable.level,
                                ltable.parent)
        next_parents = []
        next_children = []
        for row in rows:

            this = row.id
            level = int(row.level[1])
            parent = row.parent

            if this not in sdata:
                sdata[this] = {}
            data = sdata[this]
            data["n"] = row.name
            data["l"] = level
            data["f"] = parent
            data["p"] = 0
            data["i"] = dict(idefaults)
            data["x"] = this not in locations

            if level > 0 and parent:
                if parent in parents and \
                   level < MAX_LEVEL and \
                   parent in locations:
                    pass
                    #next_parents.append(this)
                elif this in children and parent not in sdata:
                    next_children.append(parent)
        parents = next_parents
        children = next_children

    # Population
    if level in ("L0", "L1"):
        # Lookup direct
        ddtable = s3db.stats_demographic_data
        query = (ddtable.location_id.belongs(sdata.keys())) & \
                (ddtable.parameter_id == session.s3.population_id)
        rows = db(query).select(ddtable.location_id,
                                ddtable.value,
                                orderby=~ddtable.date)
        location_ids = []
        seen = location_ids.append
        for row in rows:
            location_id = row.location_id
            if location_id not in location_ids:
                seen(location_id)
                sdata[location_id]["p"] = row.value

    # Look up aggregates
    atable = s3db.vulnerability_aggregate
    query = (atable.location_id.belongs(sdata.keys())) & \
            (atable.parameter_id == session.s3.population_id)
    rows = db(query).select(atable.location_id,
                            atable.sum,
                            atable.ward_count,
                            atable.reported_count,
                            orderby=~atable.date)
    location_ids = []
    seen = location_ids.append
    for row in rows:
        location_id = row.location_id
        if location_id not in location_ids:
            seen(location_id)
            data = sdata[location_id]
            if not data["p"]:
                data["p"] = row.sum
            data["t"] = row.ward_count
            data["r"] = row.reported_count

    # Calculate ward_count manually for Lx without aggregates
    #commune_level = "L%s" % MAX_LEVEL
    #for location_id in sdata.keys():
    #    data = sdata[location_id]
    #    if "t" not in data:
    #        data["r"] = 0
    #        # @ToDo: optimise this to do in-bulk rather than per-record
    #        data["t"] = len(gis.get_children(location_id, level=commune_level))

    # Indicators
    query = (atable.location_id.belongs(sdata.keys())) & \
            (atable.parameter_id.belongs(indicator_pids))
    rows = db(query).select(atable.location_id,
                            atable.parameter_id,
                            atable.median)
    for row in rows:
        location_id = row.location_id
        location_data = sdata[location_id]
        if "i" not in location_data:
            location_data["i"] = dict(idefaults)
        location_data["i"][row.parameter_id] = row.median

    # Return as script
    script = '''sdata=%s\n''' % json.dumps(sdata)
    response.headers["Content-Type"] = "application/json"
    return script

# -----------------------------------------------------------------------------
def reportFilter(filter_request, loc_id, loc_level):
    """
        Helper function to extract the selections from the side panel
        and generate a resource filter
    """

    vdoc_table = db.vulnerability_document
    gtable = db.gis_location
    query = (vdoc_table.deleted != True) & \
            (vdoc_table.location_id == gtable.id)
    if loc_id != -1:
        next_loc_level = "L%s" % (int(loc_level[1:]) +1)
        child_locations = gis.get_children(loc_id, next_loc_level)
        if len(child_locations) == 0:
            query &= (vdoc_table.location_id == loc_id)
        else:
            child_ids = [row.id for row in child_locations]
            child_ids.append(loc_id) # include the selected location
            query &= (vdoc_table.location_id.belongs(child_ids))
    else:
        # Show the country-level
        query &= (gtable.level == "L0")

    if filter_request["from_date"]:
        query &= (vdoc_table.date >= filter_request["from_date"])
    if filter_request["to_date"]:
        query &= (vdoc_table.date <= filter_request["to_date"])

    document_types = ["vca"]
    indicator = (vdoc_table.document_type == "vca")
    if "indicator" in filter_request:
        document_types.append("indicator")
    if "demographics" in filter_request:
        document_types.append("demographic")
    if "map" in filter_request:
        document_types.append("map")
    if "images" in filter_request:
        document_types.append("image")
    if "reports" in filter_request:
        document_types.append("other")
    if len(document_types) == 1:
        query &= (vdoc_table.document_type == "vca")
    else:
        query &= (vdoc_table.document_type.belongs(document_types))

    if "myReports" in filter_request:
        user_id = auth.user.id
        query &= ((vdoc_table.approved_by == user_id)
                  | (vdoc_table.created_by == user_id))

    if "text" in filter_request and filter_request["text"] != "":
        utable = auth.settings.table_user
        text = "%%%s%%" % filter_request["text"].lower()
        query &= (vdoc_table.location_id == gtable.id)
        query &= (vdoc_table.created_by == utable.id)
        query &= ((gtable.name.lower().like(text))
                  | (utable.first_name.lower().like(text))
                  | (utable.last_name.lower().like(text)))

    # Now ensure that all unapproved records are added to the return list
    query = ((vdoc_table.deleted != True) & \
             (vdoc_table.approved_by == None) & \
             (vdoc_table.location_id == gtable.id)
            ) | (query)

    return query

# -------------------------------------------------------------------------
def report_group(row):
    """
        Virtual field to show the group that the report belongs to
        used by vulnerability/report
    """

    if "vulnerability_document" in row:
        row = row["vulnerability_document"]

    # These get i18n later
    if row.approved_by is None:
        return "Approval pending"
    elif row.document_type == "vca":
        return "VCA Report"
    else:
        return "Report"

# -----------------------------------------------------------------------------
def reportDataTable():
    """
        Return a dataTable using the selected filter options
    """

    from s3.s3data import S3DataTable

    vdoc_table = s3db.vulnerability_document
    vdoc_table.group = Field.Lazy(report_group)
    gtable = db.gis_location

    # -------------------------------------------------------------------------
    # Set up custom represents
    # -------------------------------------------------------------------------
    def location_repr(id):
        """
            Return the location name (level) wrapped in a span
        """

        if not id:
            repr_text = messages["NONE"]
        else:
            row = locations.get(id, None)
            if not row:
                repr_text = messages.UNKNOWN_OPT
            else:
                level = loc_labels[row["level"]]
                repr_text = "%s (%s)" % (row["name"], level)
        return SPAN(repr_text, _class="communeCell")

    # -------------------------------------------------------------------------
    def submitted_repr(id):
        """
            Return the initial of the first name and the complete last name
        """

        if not id:
            repr_text = T("Imported data")
        else:
            row = users.get(id, None)
            if row:
                repr_text = "%s. %s" % (row["first_name"][0], row["last_name"])
            else:
                repr_text = messages.UNKNOWN_OPT
        return repr_text

    # -------------------------------------------------------------------------
    def approved_repr(id):
        """
            Return the initials of the first and the last name
        """

        if id is None:
            repr_text = APPROVAL_PENDING
        elif id == 0:
            repr_text = APPROVED
        else:
            row = users.get(id, None)
            if row:
                repr_text = T("Approved by %(first_name)s.%(last_name)s") % \
                    dict(first_name = row["first_name"][0],
                         last_name = row["last_name"][0])
            else:
                repr_text = messages.UNKNOWN_OPT
        return repr_text

    # -------------------------------------------------------------------------
    def action_repr(id):
        """
            Return the action button for this row
        """

        approved = approvals.get(id, None)
        if approved != None:
            repr_text = A(VIEW,
                          _id = id,
                          _class = "viewButton",
                          _href = "javascript:viewReportDetails(%s);" % id
                          )
        else:
            repr_text = A(REVIEW,
                          _id = id,
                          _class = "reviewButton",
                          _href = "javascript:showReportDetails(%s);" % id
                          )

        repr_text.append(A(CLOSE,
                           _class = "closeReviewButton",
                           _href = "javascript:hideReportDetails(%s);" % id
                           ))
        return repr_text

    filter_request = request.post_vars
    loc_level = -1
    if filter_request:
        if "location_id" in filter_request:
            loc_id = filter_request["location_id"]
            if loc_id == "-1":
                loc_id = -1
            else:
                row = db(gtable.id == loc_id).select(gtable.level,
                                                     gtable.path,
                                                     limitby=(0, 1)
                                                     ).first()
                try:
                    loc_level = row.level
                except:
                    # Invalid location ID
                    loc_id = -1
                else:
                    if loc_level == "L0":
                        L0 = loc_id
                    else:
                        L0 = row.path.split("/")[0]
        else:
            loc_id = -1
        filter = reportFilter(filter_request, loc_id, loc_level)

    if loc_id == -1:
        loc_labels = gis.get_location_hierarchy()
    else:
        loc_labels = gis.get_location_hierarchy(location=L0)

    #############################################################
    # Note if list_fields are changed here then they also need
    # to be changed in index, where the table is initialised
    #############################################################
    if loc_level == -1:
        loc_list_field = "location_id$L0"
        loc_group_field = "gis_location.L0"
    elif loc_level == "L0":
        loc_list_field = "location_id$L1"
        loc_group_field = "gis_location.L1"
    elif loc_level == "L1":
        loc_list_field = "location_id$L2"
        loc_group_field = "gis_location.L2"
    elif loc_level == "L2":
        loc_list_field = "location_id$L3"
        loc_group_field = "gis_location.L3"
    elif loc_level == "L3":
        loc_list_field = "location_id$L3"
        loc_group_field = "gis_location.L3"
    # @ToDo: Support countries with L4s/L5s
    #elif loc_level == "L4":
    #    loc_list_field = "location_id$L4"
    #    loc_group_field = "gis_location.L4"

    list_fields = [(T("Action"), "id"),
                   (T("Date"), "date"),
                   (T("Location"), "location_id"),
                   # Field.Lazy
                   "group",
                   loc_list_field,
                   "document_type",
                   (T("Submitted by"), "created_by"),
                   (T("Status"), "approved_by"),
                   ]

    # Ensure that we also get the records awaiting for approval
    resource = s3db.resource("vulnerability_document", unapproved=True)
    if filter_request:
        resource.add_filter(filter)
        
    data = resource.select(list_fields,
                           orderby=~vdoc_table.date,
                           limit=None,
                           count=True,
                           represent=True,
                           raw_data=True)

    numrows = data["numrows"]
    if numrows > 0:
    
        # Do represents in-bulk
        # @ToDo: Replace with S3Represents & define before select
        approvals = {}
        locations = []
        lappend = locations.append
        users = []
        uappend = users.append
        
        rows = data["rows"]
        for row in rows:
            _row = row["_row"]
            location_id = _row["vulnerability_document.location_id"]
            if location_id and location_id not in locations:
                lappend(location_id)
            user_id = _row["vulnerability_document.created_by"]
            if user_id and user_id not in users:
                uappend(user_id)
            user_id = _row["vulnerability_document.approved_by"]
            approvals[_row.id] = user_id
            if user_id and user_id not in users:
                uappend(user_id)

        lrows = db(gtable.id.belongs(locations)).select(gtable.id,
                                                        gtable.name,
                                                        gtable.level,
                                                        gtable.L1,
                                                        gtable.L2)
        locations = lrows.as_dict()
        utable = auth.settings.table_user
        urows = db(utable.id.belongs(users)).select(utable.id,
                                                    utable.first_name,
                                                    utable.last_name)
        users = urows.as_dict()

        APPROVED = T("Approved")
        APPROVAL_PENDING = T("Approval pending")
        VIEW = T("View")
        REVIEW = T("Review")
        CLOSE = T("Close")
        vdoc_table.location_id.represent = location_repr
        vdoc_table.created_by.represent = submitted_repr
        vdoc_table.approved_by.represent = approved_repr
        vdoc_table.id.represent = action_repr

        # The types are fixed and will always be displayed (even if empty)
        type_totals = {"Approval pending" : 0,
                       "VCA Report" : 0,
                       "Report" : 0
                       }
        # Calculate the report group totals
        location_totals = {}
        if loc_level != -1:
            loc_label = loc_labels["L%s" % (int(loc_level[1:]) + 1)]
        for item in rows:
            # Collect the type totals
            group = item["vulnerability_document.group"]
            if not group:
                group = "Report"
            type_totals[group] += 1
            # Collect the Location sub totals
            if item[loc_group_field] == "None":
                # If the group field is none then use the location for the group
                # This will happen for any report for the selected location
                #location = item["vulnerability_document.location_id"].components[0]
                # This gives invalid Unicode conversion & anyway doesn't seem useful
                continue
            else:
                if loc_level != -1:
                    location = "%s (%s)" % (item[loc_group_field], loc_label)
                else:
                    location = item[loc_group_field]
            # Amend the representation
            item[loc_group_field] = location
            # Populate the groupTotals to be read by dataTables
            loc_code = "%s_%s" % (group, s3_unicode(location))
            if loc_code in location_totals:
                location_totals[loc_code] += 1
            else:
                location_totals[loc_code] = 1
        group_totals = {
            unicode(T("Approval pending")) : type_totals["Approval pending"],
            unicode(T("VCA Reports")) : type_totals["VCA Report"],
            unicode(T("Reports")) : type_totals["Report"]
            }
        rfields = data["rfields"]
        dt = S3DataTable(rfields, rows, orderby=~vdoc_table.date)
        # No need as hidden when used for Grouping
        #if loc_level != -1:
        #    # Amend the column label
        #    dt.heading[loc_group_field] = loc_label
        dt.defaultActionButtons(resource)
        if request.extension == "html":
            level_1_titles = [["Approval pending", T("Approval pending")],
                              ["VCA Report", T("VCA Reports")],
                              ["Report", T("Reports")],
                              ]
            report = dt.html(numrows,
                             numrows,
                             "report",
                             dt_action_col = -1,
                             dt_displayLength = numrows,
                             dt_bFilter = "false",
                             dt_pagination = "false",
                             dt_sDom = "t",
                             dt_group = [3, 4],
                             dt_group_totals = [group_totals, location_totals],
                             dt_group_titles = [level_1_titles],
                             dt_group_types = ["text", "none"],
                             dt_group_space = "true",
                             dt_shrink_groups = "accordion",
                             # Pagination done client-side currently!
                             dt_ajax_url = None,
                             #dt_ajax_url = URL(c="vulnerability",
                             #                  f="report",
                             #                  extension="aadata",
                             #                  vars={"id": "report"},
                             #                  ),
                             )
            reportCount = T("%(count)s Entries Found") % dict(count=numrows)
            report.append(INPUT(_type="hidden",
                                _id="reportCount",
                                _name="config",
                                _value=reportCount))
            return str(report)
        elif request.extension == "aadata":
            # Unsupported
            raise
    else:
        return ""

# -----------------------------------------------------------------------------
def getReportDetails():
    """
        Method to get the details of a report from the vulnerability_document id

        It will build the custom display, which is essentially a form
        wrapped around a table, if buttons are required then they will be added
        allowing for the report to be approved or rejected.
    """

    id = request.get_vars.id
    vdoc_table = s3db.vulnerability_document
    vdoc = db(vdoc_table.id == id).select(vdoc_table.name,
                                          vdoc_table.document_type,
                                          vdoc_table.doc_id,
                                          vdoc_table.source_id,
                                          limitby=(0, 1)).first()
    document_type = vdoc.document_type
    valid = True
    if document_type == "indicator":
        # Get the data for this report
        vdtable = db.vulnerability_data
        vitable = db.vulnerability_indicator
        query = (vdtable.deleted == False) & \
                (vdtable.source_id == vdoc.source_id) & \
                (vitable.parameter_id == vdtable.parameter_id)
        rows = db(query).select(vdtable.value,
                                vitable.name,
                                orderby=vitable.posn)
        # Build the custom table
        table = TABLE(TR(TH(_class="indicatorLabels"),
                         TH(DIV(1), _class="indicator1"),
                         TH(DIV(2), _class="indicator2"),
                         TH(DIV(3), _class="indicator3"),
                         TH(DIV(4), _class="indicator4"),
                         TH(DIV(5), _class="indicator5"),
                         ),
                      TR(TH(),
                         TH(SPAN(XML("&larr;"), _class="arrow"),
                            " %s" % T("LOW RESILIENCE"),
                            _colspan=2),
                         TH(" %s" % T("HIGH RESILIENCE"),
                            SPAN(XML("&rarr;"), _class="arrow"),
                            _class="highResilienceLabel",
                            _colspan=3)
                        ),
                      _class="indicatorsTable")

        mark = XML("<mark>*</mark>")
        tr_class = "white"
        for row in rows:
            tr_class = "gray" if tr_class == "white" else "white"
            tr = TR(_class=tr_class)
            name = row.vulnerability_indicator.name
            td = TD(mark, _class="indicatorLabels")
            td.append(name)
            tr.append(td)
            value = int(row.vulnerability_data.value)
            for i in range(5):
                option = INPUT(_type = "radio",
                               _name = name,
                               _value = i + 1,
                               value = value,
                               _disabled = "disabled",
                               )
                tr.append(option)
            table.append(tr)
    elif document_type == "demographic":
        # Get the data for this report
        ddtable = s3db.stats_demographic_data
        sdtable = db.stats_demographic
        query = (ddtable.deleted == False) & \
                (ddtable.source_id == vdoc.source_id) & \
                (sdtable.parameter_id == ddtable.parameter_id)
        rows = db(query).select(ddtable.value,
                                ddtable.location_id,
                                sdtable.name,
                                orderby = sdtable.name)
        # Build the custom table
        table = TABLE(_class = "demographicsTable")
        table.append(TR(TD(vdoc.name, _colspan=3)))
        tr_class = "grey"
        location_represent = s3db.gis_LocationRepresent()
        for row in rows:
            tr_class = "grey" if tr_class == "white" else "white"
            tr = TR(_class = tr_class)
            name = row.stats_demographic.name
            tr.append(TD(name, _class = "demoLabel"))
            value = IS_INT_AMOUNT().represent(row.stats_demographic_data.value)
            tr.append(TD(value, _class = "demoStatistic"))
            location = location_represent(row.stats_demographic_data.location_id)
            tr.append(TD(location, _class = "demoSource"))
            table.append(tr)
    elif document_type in ("map", "image"):
        ditable = s3db.doc_image
        record = db(ditable.doc_id == vdoc.doc_id).select(ditable.id,
                                                          ditable.name,
                                                          ditable.file,
                                                          ditable.comments,
                                                          limitby=(0, 1)
                                                          ).first()
        if record:
            size = (250, 250)
            image = s3db.pr_image_represent(record.file, size=size)
            size = s3db.pr_image_size(image, size)
            desc = DIV(record.comments, _class="imageDesc")
            filename = record.name
            url_small = URL(c="default", f="download", args=image)
            alt = record.comments if record.comments else filename
            thumb = IMG(_src=url_small,
                        _alt=alt,
                        _width=size[0],
                        _height=size[1]
                        )
            url_full = URL(c="default", f="download", args=record.file)
            download = A(T("Download"), _class="download", _href=url_full)
            view = A(T("View full size"),
                     _class="download",
                     _href=URL(c="vulnerability", f="view_image",
                               args=record.id),
                     _target="blank")
            table = TABLE(_class = "imageTable")
            table.append(TR(TD(thumb, _colspan=4)))
            table.append(TR(TD(desc),
                            TD(download),
                            TD(DIV(" | ", _class="divider")),
                            TD(view),
                            _class="mapRow"))
        else:
            valid = False
    elif document_type in ("other", "vca"):
        doctable = s3db.doc_document
        record = db(doctable.doc_id == vdoc.doc_id).select(doctable.id,
                                                           doctable.file,
                                                           doctable.name,
                                                           limitby=(0, 1)
                                                           ).first()
        if record:
            desc = DIV(record.name, _class="imageDesc")
            url = URL(c="default", f="download", args=record.file)
            download = A(T("Download"), _class="download", _href=url)
            table = TABLE(_class="imageTable")
            table.append(TR(TD(desc),
                            TD(download),
                            _class="mapRow"))
        else:
            valid = False
    else:
        valid = False

    # Place the table in a form and attach the buttons (if required)
    form = FORM(_id="form%s" % id)
    if valid:
        form.append(table)
    else:
        form.append(DIV(T("No data available"), _class="mapRow"))
    if request.args(0) == "review":
        if valid:
            form.append(INPUT(_type="button", _name="Approve%s" % id,
                              _value=T("Approve"), _class="approveButton"))
        form.append(INPUT(_type="button", _name="Decline%s" % id,
                          _value=T("Decline"), _class="declineButton"))
    return str(form)

# -----------------------------------------------------------------------------
def view_image():
    """
        View a Fullscreen version of an Image - called from Reports
    """

    try:
        id = request.args[0]
    except:
        return "Need to provide the id of the Image"
    table = s3db.doc_image
    record = db(table.id == id).select(table.name,
                                       table.file,
                                       table.comments,
                                       limitby=(0, 1)).first()
    desc = DIV(record.comments, _class="imageDesc")
    filename = record.name
    url = URL(c="default", f="download", args=record.file)
    alt = record.comments if record.comments else filename
    image = IMG(_src=url, _alt=alt)
    output = Storage(image = image,
                     desc = desc,
                     )
    return output

# -----------------------------------------------------------------------------
def approveReport(id):
    """
        Function to approve a report
    """

    # Approve the vulnerability_document record
    resource = s3db.resource("vulnerability_document", id=id, unapproved=True)
    resource.approve()
    # Read the record details
    vdoc_table = db.vulnerability_document
    record = db(vdoc_table.id == id).select(vdoc_table.document_type,
                                            vdoc_table.doc_id,
                                            vdoc_table.source_id,
                                            limitby=(0, 1)).first()
    # Approve the linked records
    document_type = record.document_type
    if document_type == "indicator":
        tablename = "vulnerability_data"
        table = s3db[tablename]
        query = (table.source_id == record.source_id)
        agg_function = "vulnerability_update_aggregates"
    elif document_type == "demographic":
        tablename = "stats_demographic_data"
        table = s3db[tablename]
        query = (table.source_id == record.source_id)
        agg_function = "stats_demographic_update_aggregates"
    elif document_type in ("map", "image"):
        tablename = "doc_image"
    elif document_type in ("vca", "other"):
        tablename = "doc_document"
        query = (s3db[tablename].doc_id == record.doc_id)
    else:
        s3base.s3_debug("approveReport unknown type", document_type)
        return False
    resource = s3db.resource(tablename, filter=query, unapproved=True)
    resource.approve()
    if document_type in ("indicator", "demographic"):
        # Rebuild the relevant aggregates
        rows = resource.select(fields=["data_id",
                                       "parameter_id",
                                       "date",
                                       "location_id",
                                       "value"],
                               as_rows=True)
        s3task.async(agg_function, args=[rows])
    return True

# -----------------------------------------------------------------------------
def declineReport(id):
    """
        Function to Decline a report
    """

    # Find the type of report that we have
    vdoc_table = s3db.vulnerability_document
    record = db(vdoc_table.id == id).select(vdoc_table.document_type,
                                            vdoc_table.doc_id,
                                            vdoc_table.source_id,
                                            limitby=(0, 1)).first()
    document_type = record.document_type
    # Now that we have the necessary data, reject the report
    resource = s3db.resource("vulnerability_document", id=id, unapproved=True)
    resource.reject()
    # Reject the linked data
    if document_type in ("indicator", "demographic"):
        source_id = record.source_id
        # Reject the stats_data records
        query = (db.stats_data.source_id == source_id)
        resource = s3db.resource("stats_data", filter=query, unapproved=True)
        resource.reject()
        # Reject the instance records
        if document_type == "indicator":
            query = (s3db.vulnerability_data.source_id == source_id)
            resource = s3db.resource("vulnerability_data", filter=query,
                                     unapproved=True)
            resource.reject()
        elif document_type == "demographic":
            query = (s3db.stats_demographic_data.source_id == source_id)
            resource = s3db.resource("stats_demographic_data", filter=query,
                                     unapproved=True)
            resource.reject()
    elif document_type in ("image", "map"):
        query = (s3db.doc_image.doc_id == record.doc_id)
        resource = s3db.resource("doc_image", filter=query, unapproved=True)
        resource.reject()
    elif document_type in ("other", "vca"):
        query = (s3db.doc_document.doc_id == record.doc_id)
        resource = s3db.resource("doc_document", filter=query, unapproved=True)
        resource.reject()
    else:
        return False
    return True

# -----------------------------------------------------------------------------
def report():
    """
        Controller to list/view/approve/reject Reports.
        - list uses a suitably-filtered dataTable
    """

    s3.no_formats = True
    arg = request.args(0)
    if arg == "filter":
        data = reportDataTable()
    elif arg == "review" or arg == "view":
        data = getReportDetails()
    elif arg == "approve":
        # Check authorization
        permitted = auth.s3_has_permission("approve", "vulnerability_document")
        if not permitted:
            data = s3_unicode(T("You are not permitted to approve documents"))
        else:
            id = request.post_vars.id
            if approveReport(id):
                data = reportDataTable()
            else:
                data = s3_unicode(T("Failed to approve"))
    elif arg == "decline":
        # Check authorization
        permitted = auth.s3_has_permission("approve", "vulnerability_document")
        if not permitted:
            data = s3_unicode(T("You are not permitted to approve documents"))
        else:
            id = request.post_vars.id
            if declineReport(id):
                data = reportDataTable()
            else:
                data = s3_unicode(T("Decline failed"))
    else:
        date_widget = S3DateWidget(format="yy-mm-dd", future=0)
        to_date = Field("to_date")
        to_date.tablename = to_date._tablename = ""
        from_date = Field("from_date")
        from_date.tablename = from_date._tablename = ""
        report = reportDataTable()
        data = {"filter" : {"to_date" : str(date_widget(to_date, None)),
                            "from_date" : str(date_widget(from_date, None)),
                            },
                "report" : report
                }

    response.headers["Content-Type"] = "application/json"
    return json.dumps(data)

# -----------------------------------------------------------------------------
def submitData():
    """ Controller to manage the AJAX import of vulnerability data """

    # Get the action to be performed
    action = request.post_vars.action
    if action == "vulnerability":
        return import_vul_ui()
    elif action == "vulnerability_part1":
        return import_vul_csv_part1()
    elif action == "vulnerability_part2":
        return import_vul_csv_part2()
    elif action in ("map", "image", "other", "vca"):
        return import_document(action)
    elif action == "demographics":
        return import_demo_ui()
    elif action == "demographics_part1":
        return import_demo_csv_part1()
    elif action == "demographics_part2":
        return import_demo_csv_part2()

# -----------------------------------------------------------------------------
def import_vul_ui():
    """
        Controller to add a new set of vulnerability indicators
        which have been input direct into the GUI
    """

    date = request.utcnow
    location_id = request.post_vars.location
    update_super = s3db.update_super

    # First create the stats_source
    ss_table = s3db.stats_source
    id = ss_table.insert(name = "Vulnerability indicators submitted through UI")
    update_super(ss_table, dict(id=id))
    # Read the source_id
    source_id = db(ss_table.id == id).select(ss_table.source_id,
                                             limitby=(0, 1)
                                             ).first().source_id

    # Next create the vulnerability_document
    vdoc_table = s3db.vulnerability_document
    id = vdoc_table.insert(document_type = "indicator",
                           date = date,
                           location_id = location_id,
                           source_id = source_id,
                           )
    update_super(vdoc_table, dict(id=id))

    # Get the list of indicators
    itable = s3db.vulnerability_indicator
    rows = db(itable.deleted == False).select(itable.posn,
                                              itable.parameter_id,
                                              orderby=itable.posn)
    vd_table = db.vulnerability_data
    for row in rows:
        id = vd_table.insert(parameter_id = row.parameter_id,
                             location_id = location_id,
                             value = request.vars[str(row.posn)],
                             date = date,
                             source_id = source_id,
                             )
        update_super(vd_table, dict(id=id))

# -----------------------------------------------------------------------------
def import_vul_csv_part1():
    """
        Controller to manage the first phase of the import of vulnerability
        indicators from CSV
    """

    from gluon.serializers import json as jsons
    try:
        file = request.vars.file.file
    except:
        response.headers["Content-Type"] = "application/json"
        return jsons({"Error": s3_unicode(T("File missing"))})

    # Check authorization
    authorised = auth.s3_has_permission("create", "vulnerability_data")
    if not authorised:
        response.headers["Content-Type"] = "application/json"
        return jsons({"Error": s3_unicode(T("You are not permitted to upload files"))})

    # Do a normal CSV import
    output = s3_rest_controller("vulnerability", "data",
                                csv_stylesheet="data.xsl")
    if "Error" in output:
        response.headers["Content-Type"] = "application/json"
        return jsons({"Error": s3_unicode(output["Error"])})

    upload_id = output[0]
    item_ids = output[1]
    data = output[2]

    # Loop through all the vulnerability_data & group by source_id
    from lxml import etree
    loc_labels = {}
    ele_dict = {}
    for value in data:
        if value["s3_import_item.error"]:
            response.headers["Content-Type"] = "application/json"
            return jsons({"Error": value["s3_import_item.error"]})
        ele = value["s3_import_item.element"]
        ele = s3xml.xml_decode(ele)
        try:
            element = etree.fromstring(ele)
        except:
            return T("No valid data in the file")

        data_dict = {}
        data = element.findall("data")
        for item in data:
            f = item.get("field", None)
            v = item.get("value", None)
            data_dict[f] = v
        references = element.findall("reference")
        for reference in references:
            f = reference.get("field", None)
            if f == "source_id":
                source_tuid = reference.get("tuid", None)
                # tuid: stats_source//Level/Country/L1/L2/L3//Date
                try:
                    # Extract the Location
                    loc_parts = source_tuid.split("//")[1].split("/")
                    data_dict["location"] = loc_parts[-1]
                    level = loc_parts[0]
                    country_code = loc_parts[1]
                    if country_code not in loc_labels:
                        country_name = gis.get_country(country_code, key_type="code")
                        table = s3db.gis_location
                        country_id = db(table.name == country_name).select(table.id,
                                                                           limitby=(0, 1)).first().id
                        lx_labels = gis.get_location_hierarchy(location=country_id)
                        loc_labels[country_code] = lx_labels
                    else:
                        lx_labels = loc_labels[country_code]
                    data_dict["loc_label"] = lx_labels[level]
                except:
                    # Invalid source_tuid
                    continue
            elif f == "parameter_id":
                t = reference.get("tuid", None)
                try:
                    indicator = t.split("/")[1]
                    data_dict[f] = indicator
                except:
                    # We can't do anything with a data element not linked to an Indicator
                    continue
        if source_tuid in ele_dict:
            ele_dict[source_tuid].append(data_dict)
        else:
            ele_dict[source_tuid] = [data_dict]

    # Now prepare the data for display in the UI
    from datetime import datetime
    data_list = []
    for (key, group) in ele_dict.items():
        row = group[0]
        group_dict = dict(
            group = key,
            date = datetime.strptime(row["date"], "%Y-%m-%d").strftime("%d-%b-%y"),
            location = "%s %s" % (row["location"], row["loc_label"])
            )
        indicator_dict = {}
        param_len = len(row["parameter_id"][0]) + 1 # include the separator
        for row in group:
            param = row["parameter_id"]
            indicator_dict[param] = row["value"]
        group_dict["data"] = indicator_dict
        data_list.append(group_dict)

    # Return the output
    response.headers["Content-Type"] = "application/json"
    return jsons({"upload_id" : upload_id,
                  "items" : item_ids,
                  "data" : data_list
                  })

# -----------------------------------------------------------------------------
def import_vul_csv_part2():
    """
        Controller to manage the second phase of the import of vulnerability
        indicators from CSV
    """

    job_id = request.vars.job
    if not job_id:
        return "Error No Job ID's provided"

    output = s3_rest_controller("vulnerability", "data",
                                csv_stylesheet="data.xsl")
    totalRecords = output[0]
    totalErrors = output[1]
    totalIgnored = output[2]

    from gluon.serializers import json as jsons

    response.headers["Content-Type"] = "application/json"
    return jsons({"totalRecords" : totalRecords,
                  "totalErrors" : totalErrors,
                  "totalIgnored" : totalIgnored
                  })

# -----------------------------------------------------------------------------
def import_document(document_type):
    """
        Controller to store a document
    """

    if document_type in ("map", "image"):
        image = True
        doc_table = s3db.doc_image
    else:
        image = False
        doc_table = s3db.doc_document
    file = request.vars.file
    real_filename = file.filename
    new_filename = doc_table.file.store(file, real_filename)
    date = request.utcnow
    location_id = request.vars.location
    # Create the doc_document or doc_image
    id = doc_table.insert(file = new_filename,
                          name = real_filename,
                          date = date,
                          comments = request.vars.desc,
                          location_id = location_id,
                          )
    s3db.update_super(doc_table, dict(id=id))
    # Read the doc_id
    doc_id = db(doc_table.id == id).select(doc_table.doc_id,
                                           limitby=(0, 1)
                                           ).first().doc_id
    if image:
        # Create a thumbnail of the image
        s3db.pr_image_resize(file.file,
                             new_filename,
                             real_filename,
                             (250, 250),
                             )

    # Create the vulnerability_document
    s3db.vulnerability_document.insert(doc_id = doc_id,
                                       document_type = document_type,
                                       date = date,
                                       location_id = location_id,
                                       )

# -----------------------------------------------------------------------------
def import_demo_ui():
    """
        Controller to store a new set of demographic data which has been input
        direct into the GUI
    """

    vdoc_table = s3db.vulnerability_document
    ss_table = db.stats_source
    update_super = s3db.update_super
    location_id = request.vars.location
    date_submitted = request.vars.reportDate

    # First create the demographic_documents (one per source)
    last_source = ""
    source_list = {} # the sources
    seen_source = [] # the sources that have already been seen
    data = []
    for x in range(7):
        value = request.vars["demoField%s" % x]
        source = request.vars["sourceField%s" % x]
        if source == "":
            # Allow user to enter the source in just 1 field to use for all subsequent
            source = last_source
        else:
            last_source = source
        date = request.vars["reportDate%s" % x]
        data.append((value, source, date))
        if source != "" and value != "":
            # Add the source if we have a value
            if source not in seen_source:
                seen_source.append(source)
                # Create the stats_source
                # - note that this means we'll get multiple copies of the same sources
                # - however approval is done by vulnerability_document, so each vulnerability_document needs a unique source :/
                id = ss_table.insert(name = source)
                update_super(ss_table, dict(id=id))
                source_id = db(ss_table.id == id).select(ss_table.source_id,
                                                         limitby=(0, 1)
                                                         ).first().source_id
                # Now create the vulnerability_document
                id = vdoc_table.insert(name = source,
                                       date = date_submitted,
                                       location_id = location_id,
                                       document_type = "demographic",
                                       source_id = source_id,
                                       )
                update_super(vdoc_table, dict(id=id))
                source_list[source] = source_id

    # Now get the Demographic parameter_ids
    demo_string_list = ["Population",
                        "Male",
                        "Female",
                        "Over 60",
                        "Under 5",
                        "Households",
                        "Households below poverty line"
                        ]
    sd_table = s3db.stats_demographic
    rows = db(sdtable.name.belongs(demo_string_list)).select(sd_table.name,
                                                             sd_table.parameter_id)
    # Sort these into the order of the UI
    demo_recs = {}
    for record in rows:
        demo_recs[record.name] = record.parameter_id
    demographics_list = []
    for demo_string in demo_string_list:
        if demo_string in demo_recs:
            demographics_list.append(demo_recs[demo_string])
        else:
            demographics_list.append(None) # Should never have this

    # Create the demographic_data records
    sdd_table = db.stats_demographic_data
    for x in range(7):
        _data = data[x]
        if _data[0] != "":
            id = sdd_table.insert(parameter_id = demographics_list[x],
                                  location_id = location_id,
                                  value = _data[0],
                                  date = _data[2],
                                  source_id = source_list[_data[1]],
                                  )
            update_super(sdd_table, dict(id=id))

# -----------------------------------------------------------------------------
def import_demo_csv_part1():
    """
        Controller to manage the first phase of the import of demographic data
        from CSV
    """

    from gluon.serializers import json as jsons
    try:
        file = request.vars.file.file
    except:
        response.headers["Content-Type"] = "application/json"
        return jsons({"Error": s3_unicode(T("File missing"))})

    # Check authorization
    permitted = auth.s3_has_permission
    authorised = permitted("create", "stats_demographic_data")
    if not authorised:
        response.headers["Content-Type"] = "application/json"
        return jsons({"Error": s3_unicode(T("You are not permitted to upload files"))})

    request.controller = "stats" # Need to set the controller to stats
    output = s3_rest_controller("stats", "demographic_data",
                                csv_stylesheet="demographic_data.xsl")
    if "Error" in output:
        response.headers["Content-Type"] = "application/json"
        return jsons({"Error": s3_unicode(output["Error"])})
    upload_id = output[0]
    item_ids = output[1]
    data = output[2]

    # Loop through all the stats_demographic_data & group by source_id
    from lxml import etree
    loc_labels = {}
    ele_dict = {}
    for value in data:
        if value["s3_import_item.error"]:
            response.headers["Content-Type"] = "application/json"
            return jsons({"Error": value["s3_import_item.error"]})
        ele = value["s3_import_item.element"]
        ele = s3xml.xml_decode(ele)
        try:
            element = etree.fromstring(ele)
        except:
            return T("No valid data in the file")

        data_dict = {}
        data = element.findall("data")
        for item in data:
            f = item.get("field", None)
            v = item.get("value", None)
            data_dict[f] = v
        references = element.findall("reference")
        for reference in references:
            f = reference.get("field", None)
            if f == "source_id":
                source_tuid = reference.get("tuid", None)
            elif f == "location_id":
                # tuid: Level/Country/L1/L2/L3
                tuid = reference.get("tuid", None)
                if tuid:
                    try:
                        # Extract the Location
                        loc_parts = tuid.split("/")
                        data_dict["location"] = loc_parts[-1]
                        level = loc_parts[0]
                        country_code = loc_parts[1]
                        if country_code not in loc_labels:
                            country_name = gis.get_country(country_code, key_type="code")
                            table = s3db.gis_location
                            country_id = db(table.name == country_name).select(table.id,
                                                                               limitby=(0, 1)).first().id
                            lx_labels = gis.get_location_hierarchy(location=country_id)
                            loc_labels[country_code] = lx_labels
                        else:
                            lx_labels = loc_labels[country_code]
                        data_dict["loc_label"] = lx_labels[level]
                    except:
                        # Invalid location_tuid
                        continue
                else:
                    uuid = reference.get("uuid", None)
                    if uuid:
                        data_dict["loc_label"] = COUNTRY
                        country_code = uuid.split(":")[-1]
                        data_dict["location"] = gis.get_country(country_code, key_type="code")
            elif f == "parameter_id":
                t = reference.get("tuid", None)
                try:
                    demographic = t.split("/")[1]
                    data_dict[f] = demographic
                except:
                    # We can't do anything with a data element not linked to a Demographic
                    continue
        if source_tuid in ele_dict:
            ele_dict[source_tuid].append(data_dict)
        else:
            ele_dict[source_tuid] = [data_dict]

    # Now prepare the data for display in the UI
    from datetime import datetime
    data_list = []
    for (key, group) in ele_dict.items():
        row = group[0]
        group_dict = dict(
            group = key,
            date = datetime.strptime(row["date"], "%Y-%m-%d").strftime("%d-%b-%y"),
            location = "%s %s" % (row["location"], row["loc_label"])
            )
        indicator_dict = {}
        param_len = len(row["parameter_id"][0]) + 1 # include the separator
        for row in group:
            param = row["parameter_id"]
            indicator_dict[param] = row["value"]
        group_dict["data"] = indicator_dict
        data_list.append(group_dict)

    # Return the output
    response.headers["Content-Type"] = "application/json"
    return jsons({"upload_id" : upload_id,
                  "items" : item_ids,
                  "data" : data_list
                  })

# -----------------------------------------------------------------------------
def import_demo_csv_part2():
    """
        Controller to manage the second phase of the import of demographic data
        from CSV
    """

    job_id = request.vars.job
    if not job_id:
        return "Error No Job ID's provided"

    # Fake the controller for the import
    request.controller = "stats"
    output = s3_rest_controller("stats", "demographic_data",
                                csv_stylesheet="demographic_data.xsl")
    totalRecords = output[0]
    totalErrors = output[1]
    totalIgnored = output[2]

    from gluon.serializers import json as jsons

    response.headers["Content-Type"] = "application/json"
    return jsons({"totalRecords" : totalRecords,
                  "totalErrors" : totalErrors,
                  "totalIgnored" : totalIgnored
                  })

# -----------------------------------------------------------------------------
def indicator():
    """ REST Controller """

    return s3_rest_controller()

# -----------------------------------------------------------------------------
def aggregated_indicator():
    """ REST Controller """

    return s3_rest_controller()

# -----------------------------------------------------------------------------
def data():
    """ REST Controller """

    return s3_rest_controller()

# -----------------------------------------------------------------------------
def document():
    """ REST Controller """

    return s3_rest_controller()

# -----------------------------------------------------------------------------
def aggregate():
    """ REST Controller """

    def clear_aggregates(r, **attr):
        if not s3_has_role(ADMIN):
            auth.permission.fail()
        s3db.stats_demographic_rebuild_all_aggregates()
        redirect(URL(c="vulnerability",
                     f="aggregate",
                     args="",
                     ))
        
    s3db.set_method("vulnerability", "aggregate",
                    method="clear",
                    action=vulnerability_rebuild_all_aggregates)

    output = s3_rest_controller()
    return output

# -----------------------------------------------------------------------------
def handdrawn():
    """ REST Controller for Hand-drawn Maps """

    table = s3db.vulnerability_document
    s3.filter = (s3db.doc_image.doc_id == table.doc_id) & \
                (table.document_type == "map")

    return s3_rest_controller("doc", "image")

# -----------------------------------------------------------------------------
def hazard():
    """ REST Controller """

    return s3_rest_controller()

# -----------------------------------------------------------------------------
def risk():
    """ REST Controller """

    return s3_rest_controller(rheader=s3db.vulnerability_rheader)

# -----------------------------------------------------------------------------
def evac_route():
    """ REST Controller """

    return s3_rest_controller()

# END =========================================================================
